<?php
	namespace Qzn\Model;
	abstract class DatabaseQuery {
		//RegisterHandler.php
		
		/**
		 * Runs query to select username
		 * @param Database $db
		 * @return String Username
		 */
		public static function SelectUsernameWithName($user, \Qzn\Model\Database $db) {
			$sql = "SELECT Username FROM user WHERE Username = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $user);
			return $db->SelectOne($stmt);
		}
		
		/**
		 * Runs insert query to save user
		 * @param string $user
		 * @param string $hash
		 * @param string $pn
		 * @param string $email
		 */
		public static function InsertUser($user, $hash, $email, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO user (Username, Password, Email) VALUES (?, ?, ?)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("sss", $user, $hash, $email);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		
		//ProfileHandler.php
		
		/**
		 * Runs insert query to save user
		 * @param string $userPk
		 * @param string $path
		 */
		public static function InsertProfilePicture($userPk, $path, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO profilepicture (UserID, Path) VALUES (?, ?)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("is", $userPk, $path);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * Runs insert query to save user
		 * @param string $userPk
		 */
		public static function DeleteProfilePicture($userPk, \Qzn\Model\Database $db) {
			$sql = "DELETE FROM profilepicture WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("i", $userPk);
	
			$db->RunInsertQuery($stmt);
		}
		
		/**
		 * @return string
		 */
		public static function SelectProfilePicture($userPk, \Qzn\Model\Database $db) {
			$sql = "SELECT Path FROM profilepicture WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $userPk);
			
			return $db->SelectOne($stmt);
		}
				
		/**
		 * Selects array of usernames
		 * @return array
		 */
		public static function SelectProfileData($userPk, \Qzn\Model\Database $db) {
			$sql = "SELECT Username, Date, UserCatID FROM user WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $userPk);
			
			return $db->SelectThree($stmt);
		}
		
		/**
		 * @return boolean
		 */
		public static function InsertNewPassword($pw, $userPk, \Qzn\Model\Database $db) {
			$sql = "UPDATE user SET Password = ? WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("si", $pw, $userPk);
	
			return $db->UpdateQuery($stmt);
		}
		
		/**
		 * @return string $hash
		 */
		public static function GetPassword($pk, \Qzn\Model\Database $db) {
			$sql = "SELECT Password FROM user WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $pk);
			
			return $db->Select($stmt);
		}
				
		/**
		 * Selects array of friendships
		 * @return array
		 */
		public static function SelectFriendship($user1Pk, $user2Pk, \Qzn\Model\Database $db) {
			$sql = "SELECT FriendStatusID FROM friend WHERE User1ID = ? and User2ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
			
			return $db->Select($stmt);
		}
				
		/**
		 * @param user1Pk
		 * @param user2Pkn
		 */
		public static function SendFriendRequestQuery($user1Pk, $user2Pk, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO friend (User1ID, User2ID, FriendStatusID) VALUES (?, ?, 3)";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
			
			return $db->RunInsertQuery($stmt);
		}
		
		
		//MemberListHandler.php
		
				
		/**
		 * Selects array of usernames
		 * @return array
		 */
		public static function SelectMemberList(\Qzn\Model\Database $db) {
			$sql = "SELECT Username, UserID, UserCatID FROM user";
			$stmt = $db->Prepare($sql);
			
			return $db->SelectThreeSeveral($stmt);
		}
				
		/**
		 * @param string $user
		 */
		public static function RemoveMemberQuery ($userPk, \Qzn\Model\Database $db) {				
			$sql = "DELETE FROM user WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $userPk);
	
			$db->RunInsertQuery($stmt);
		}
				
		/**
		 * @param string $user
		 */
		public static function PromoteMemberQuery ($userPk, \Qzn\Model\Database $db) {				
			$sql = "UPDATE user SET UserCatID=2 WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $userPk);
	
			$db->RunInsertQuery($stmt);
		}
		
		
		//LoginHandler.php

		/**
		 * @param Database $db
		 * @param String $user
		 * @return String $temppw
		 */
		public static function InsertTempPassword($userPk, $hash, $date,  \Qzn\Model\Database $db) {
			$sql = "INSERT INTO temppassword (UserID, TempPw, Date) VALUES (?, ?, ?)";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("isi", $userPk, $hash, $date);
			
			return $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param Database $db
		 * @param String $user
		 * @return String $temppw
		 */
		public static function DeleteTempPassword($userPk, \Qzn\Model\Database $db) {
			$sql = "DELETE FROM temppassword WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $userPk);
			
			return $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param Database $db
		 * @param String $user
		 * @return String $temppw
		 */
		public static function SelectTempPassword($userPk, \Qzn\Model\Database $db) {
			$sql = "SELECT TempPw, Date FROM temppassword WHERE UserID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $userPk);
			
			return $db->SelectTwoSeveral($stmt);
		}
		
		/**
		 * @param Database $db
		 * @param String $user
		 * @return int $usercategory
		 */
		public static function SelectUserCatWithName($user, \Qzn\Model\Database $db) {			
			$sql = "SELECT UserCatID FROM user WHERE Username = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $user);
			
			return $db->SelectOne($stmt);
		}
		
		/**
		 * @param Database $db
		 * @param String $user
		 * @return int $userpk
		 */
		public static function SelectUserPkWithName($user, \Qzn\Model\Database $db) {
			$sql = "SELECT UserID FROM user WHERE Username = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $user);
			
			return $db->SelectOne($stmt);
		}
		
		/**
		 * @param Database $db
		 * @param string $user
		 * @return string Password
		 */
		public static function SelectPasswordWithName($user, \Qzn\Model\Database $db) {
			$sql = "SELECT Password FROM user WHERE Username = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $user);
			
			return $db->SelectOne($stmt);
		}
		
		
		//FriendListHandler.php	
		
		/**
		 * Selects array of friends
		 * @return array
		 */
		public static function SelectFriendList($flPk, \Qzn\Model\Database $db) {
			$sql = "SELECT u.Username, f.User1ID, f.User2ID, f.FriendStatusID FROM friend as f INNER JOIN user as u ON u.UserID = f.User1ID WHERE User2ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $flPk);
			
			$fuckIta = $db->SelectFourSeveral($stmt);
			
			$sql = "SELECT u.Username, f.User1ID, f.User2ID, f.FriendStatusID FROM friend as f INNER JOIN user as u ON u.UserID = f.User2ID WHERE User1ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $flPk);
			
			$fuckItb = $db->SelectFourSeveral($stmt);
			
			foreach ($fuckItb as $value) {
				$fuckIta[] = $value;
			}
			
			return $fuckIta;
		}
				
		/**
		 * @param string $user
		 */
		public static function RemoveFriendQuery ($user1Pk, $user2Pk, \Qzn\Model\Database $db) {				
			$sql = "DELETE FROM friend WHERE User1ID = ? and User2ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
	
			$db->RunInsertQuery($stmt);
		}
				
		/**
		 * @param string $user
		 */
		public static function BlockFriendQuery ($user1Pk, $user2Pk, \Qzn\Model\Database $db) {				
			$sql = "UPDATE friend SET FriendStatusID=2 WHERE User1ID = ? and User2ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
	
			$db->RunInsertQuery($stmt);
		}
				
		/**
		 * @param string $user
		 */
		public static function UnblockFriendQuery ($user1Pk, $user2Pk, \Qzn\Model\Database $db) {				
			$sql = "DELETE FROM friend WHERE User1ID = ? and User2ID = ? and FriendStatusID = 2";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
	
			$db->RunInsertQuery($stmt);
		}
				
		/**
		 * @param string $user
		 */
		public static function AcceptFriendQuery ($user1Pk, $user2Pk, \Qzn\Model\Database $db) {
			$sql = "UPDATE friend SET FriendStatusID=1 WHERE User1ID = ? and User2ID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("ii", $user1Pk, $user2Pk);
	
			$db->RunInsertQuery($stmt);
		}
		
		
		//WallHandler.php
		
		/**
		 * @param int $userPk
		 * @param int $messagePk
		 */
		public static function InsertMessageDislike ($userPk, $messagePk, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO messagelikes (UserID, MessageID, Likes) VALUES (?, ?, 0)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $messagePk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @param int $messagePk
		 */
		public static function DeleteMessageLike ($userPk, $messagePk, \Qzn\Model\Database $db) {
			$sql = "DELETE FROM messagelikes WHERE UserID = ? and MessageID = ?";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $messagePk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @param int $commentPk
		 */
		public static function DeleteCommentLike ($userPk, $commentPk, \Qzn\Model\Database $db) {
			$sql = "DELETE FROM commentlikes WHERE UserID = ? and CommentID = ?";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $commentPk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @param int $messagePk
		 */
		public static function InsertMessageLike ($userPk, $messagePk, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO messagelikes (UserID, MessageID, Likes) VALUES (?, ?, 1)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $messagePk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @param int $commentPk
		 */
		public static function InsertCommentDislike ($userPk, $commentPk, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO commentlikes (UserID, CommentID, Likes) VALUES (?, ?, 0)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $commentPk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @param int $commentPk
		 */
		public static function InsertCommentLike ($userPk, $commentPk, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO commentlikes (UserID, CommentID, Likes) VALUES (?, ?, 1)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("ii", $userPk, $commentPk);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 */
		public static function InsertComment($userPk, $messagePk, $comment, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO comment (UserID, MessageID, Comment) VALUES (?, ?, ?)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("iis", $userPk, $messagePk, $comment);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $userPk
		 */
		public static function InsertMessage($userPk, $message, \Qzn\Model\Database $db) {
			$sql = "INSERT INTO message (UserID, Message) VALUES (?, ?)";
			$stmt = $db->Prepare($sql);
			
			$stmt->bind_param("is", $userPk, $message);
	
			$insertId = $db->RunInsertQuery($stmt);
		}
		
		/**
		 * @param int $messagePk
		 * @return string
		 */
		public static function GetMessageLikes($messagePk, \Qzn\Model\Database $db) {
			$sql = "SELECT UserID, Likes FROM messagelikes WHERE MessageID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $messagePk);
			
			return $db->SelectTwoSeveral($stmt);
		}
		
		/**
		 * @param int $commentPk
		 * @return string
		 */
		public static function GetCommentLikes($commentPk, \Qzn\Model\Database $db) {
			$sql = "SELECT UserID, Likes FROM commentlikes WHERE CommentID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("s", $commentPk);
			
			return $db->SelectTwoSeveral($stmt);
		}
		
		/**
		 * @param int $userPk
		 * @return array($messageID, $userID, $userName, $date, $message)
		 */
		public static function GetMessages($userPk, \Qzn\Model\Database $db) {
			$sql = "SELECT m.MessageID, m.UserID, u.Username, m.Date, m.Message FROM message as m INNER JOIN user as u ON m.UserID = u.UserID WHERE m.UserID = ? ORDER BY m.Date DESC";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $userPk);
			
			return $db->SelectFiveSeveral($stmt);
		}
		
		/**
		 * @param int $messagePk
		 * @return array($messageID, $userID, $userName, $date, $message)
		 */
		public static function GetMessage($messagePk, \Qzn\Model\Database $db) {
			$sql = "SELECT m.MessageID, m.UserID, u.Username, m.Date, m.Message FROM message as m INNER JOIN user as u ON m.UserID = u.UserID WHERE m.MessageID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $messagePk);
			
			return $db->SelectFiveSeveral($stmt);
		}
		
		/**
		 * @param int $messagePk
		 * @return array($commentID, $userID, $userName, $date, $comment)
		 */
		public static function GetComments($messagePk, \Qzn\Model\Database $db) {
			$sql = "SELECT c.CommentID, c.UserID, u.Username, c.Date, c.Comment FROM comment as c INNER JOIN user as u ON c.UserID = u.UserID WHERE c.MessageID = ?";
			$stmt = $db->Prepare($sql);
			$stmt->bind_param("i", $messagePk);
			
			return $db->SelectFiveSeveral($stmt);
		}
	}
?>